﻿using System;
using System.Collections.Generic;
using System.Text;
using MS.Core.Base.Controller;
using LHS.Core.CaseRecord.DataCommons;
using System.Data;
using MS.Core.Base.Data;
using MS.Core.Constants;
using LHS.Core.TableInfo.SetUpCaseRecordForm;
using LHS.Core.CaseRecord.Classes;
using System.Collections.Specialized;
using MS.Core.Constants.Objects;
using LHS.Core.TableInfo;

namespace LHS.Core.CaseRecord.Controller
{
    public class CaseRecordController : BaseController
    {
        #region variables
        public String GetUpdatedRowSQL =
            DBBaseInfo.Hidden_Status + " = '" + Convert.ToInt32(GlobalEnumType.Status.REGISTER).ToString() + "' OR " +
            DBBaseInfo.Hidden_Status + " = '" + Convert.ToInt32(GlobalEnumType.Status.UPDATE).ToString() + "'";
        #endregion

        #region properties
        private CommonCaseRecord_Category objCaseRecord_Category;
        private CommonCaseRecord_Category _objCaseRecord_Category
        {
            get
            {
                if (objCaseRecord_Category == null)
                {
                    objCaseRecord_Category = new CommonCaseRecord_Category();
                }
                return objCaseRecord_Category;
            }
        }

        private CommonCaseRecord_Tree objCaseRecord_Tree;
        private CommonCaseRecord_Tree _objCaseRecord_Tree
        {
            get
            {
                if (objCaseRecord_Tree == null)
                {
                    objCaseRecord_Tree = new CommonCaseRecord_Tree();
                }
                return objCaseRecord_Tree;
            }
        }

        private CommonCaseRecord_Leaf objCaseRecord_Leaf;
        private CommonCaseRecord_Leaf _objCaseRecord_Leaf
        {
            get
            {
                if (objCaseRecord_Leaf == null)
                {
                    objCaseRecord_Leaf = new CommonCaseRecord_Leaf();
                }
                return objCaseRecord_Leaf;
            }
        }

        private CommonDynamicTable objDynamicTable;
        private CommonDynamicTable _objDynamicTable
        {
            get
            {
                if (objDynamicTable == null)
                {
                    objDynamicTable = new CommonDynamicTable();
                }
                return objDynamicTable;
            }
        }
        #endregion

        #region caseRecord_tree
        public DataSet getCaseRecord_TreeDS(String strCategoryID)
        {
            try
            {
                DataSet dsReturn = new DataSet();
                dsReturn.Tables.Add(_objCaseRecord_Category.getCaseRecord_CategoryDT(strCategoryID));
                dsReturn.Tables.Add(_objCaseRecord_Tree.getCaseRecord_TreeDT(strCategoryID));
                dsReturn.Tables.Add(_objCaseRecord_Leaf.getCaseRecord_LeafDT(strCategoryID));
                return dsReturn;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int insertUpdateCaseRecord_Tree(DataSet dsCaseRecord)
        {
            int intResult = 0;
            try
            {
                DataTable dtCaseRecord_Category = dsCaseRecord.Tables[CaseRecord_CategoryInfo.TABLENAME];
                DataTable dtCaseRecord_Tree = dsCaseRecord.Tables[CaseRecord_TreeInfo.TABLENAME];
                DataTable dtCaseRecord_Leaf = dsCaseRecord.Tables[CaseRecord_LeafInfo.TABLENAME];

                #region caseRecord_Category
                if (dtCaseRecord_Category.Rows.Count > 0)
                {
                    if (dtCaseRecord_Category.Rows[0][DBBaseInfo.Hidden_Status].ToString().Equals(Convert.ToInt32(GlobalEnumType.Status.REGISTER).ToString()))
                    {
                        intResult += (base.InsertRow(dtCaseRecord_Category.Rows[0]) ? 1 : 0);
                    }
                    else if (dtCaseRecord_Category.Rows[0][DBBaseInfo.Hidden_Status].ToString().Equals(Convert.ToInt32(GlobalEnumType.Status.UPDATE).ToString()))
                    {
                        intResult += base.UpdateRow(dtCaseRecord_Category.Rows[0], CaseRecord_CategoryInfo.GetKey());
                    }
                    dtCaseRecord_Category.Rows[0][DBBaseInfo.Hidden_Status] = Convert.ToInt32(GlobalEnumType.Status.HAVE_VALUE).ToString();
                }
                #endregion

                #region caseRecord_Tree
                if (dtCaseRecord_Tree.Rows.Count > 0)
                {
                    int intCurrentResult = 0;
                    foreach (DataRow drCaseRecord_Tree in dtCaseRecord_Tree.Select(GetUpdatedRowSQL))
                    {
                        if (drCaseRecord_Tree[DBBaseInfo.Hidden_Status].ToString().Equals(Convert.ToInt32(GlobalEnumType.Status.REGISTER).ToString()))
                        {
                            intCurrentResult = (base.InsertRow(drCaseRecord_Tree) ? 1 : 0);
                        }
                        else
                        {
                            intCurrentResult = base.UpdateRow(drCaseRecord_Tree, CaseRecord_TreeInfo.GetKey());
                        }
                        intResult += intCurrentResult;
                        drCaseRecord_Tree[DBBaseInfo.Hidden_Status] = Convert.ToInt32(GlobalEnumType.Status.HAVE_VALUE).ToString();

                        if (intCurrentResult > 0)
                        {
                            if (drCaseRecord_Tree[CaseRecord_TreeInfo.CRT_ISLEAF].ToString().Equals(Convert.ToInt32(clsEnumType.BooleanValue.FALES).ToString()))
                            {
                                if (dtCaseRecord_Leaf != null && dtCaseRecord_Leaf.Rows.Count > 0)
                                {
                                    foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Select(CaseRecord_LeafInfo.CRT_ID + " = '" + drCaseRecord_Tree[CaseRecord_TreeInfo.CRT_ID].ToString() + "'"))
                                    {
                                        base.DeleteRow(drCaseRecord_Leaf, CaseRecord_LeafInfo.GetKey());
                                        drCaseRecord_Leaf.Delete();
                                    }
                                    dtCaseRecord_Leaf.AcceptChanges();
                                }
                            }
                        }
                    }
                }
                #endregion

                #region caseRecord_Leaf
                if (dtCaseRecord_Leaf.Rows.Count > 0)
                {
                    foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Select(DBBaseInfo.Hidden_Status + " = '" + Convert.ToInt32(GlobalEnumType.Status.REGISTER).ToString() + "'"))
                    {
                        base.DeleteRow(drCaseRecord_Leaf, CaseRecord_LeafInfo.GetKey());
                    }
                    foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Select(GetUpdatedRowSQL))
                    {
                        if (drCaseRecord_Leaf[DBBaseInfo.Hidden_Status].ToString().Equals(Convert.ToInt32(GlobalEnumType.Status.REGISTER).ToString()))
                        {
                            intResult += (base.InsertRow(drCaseRecord_Leaf) ? 1 : 0);
                        }
                        else
                        {
                            intResult += base.UpdateRow(drCaseRecord_Leaf, CaseRecord_LeafInfo.GetKey());
                        }
                        drCaseRecord_Leaf[DBBaseInfo.Hidden_Status] = Convert.ToInt32(GlobalEnumType.Status.HAVE_VALUE).ToString();
                    }
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intResult;
        }

        public int deleteCaseRecord_Tree(String strCategoryID, DataTable dtCaseRecord_Tree, DataTable dtCaseRecord_Leaf)
        {
            int intResult = 0;
            try
            {
                if (dtCaseRecord_Tree != null && dtCaseRecord_Tree.Rows.Count > 0)
                    foreach (DataRow drCaseRecord_Tree in dtCaseRecord_Tree.Rows)
                        intResult += base.DeleteRow(drCaseRecord_Tree, CaseRecord_TreeInfo.GetKey()) ? 1 : 0;

                if (dtCaseRecord_Leaf != null && dtCaseRecord_Leaf.Rows.Count > 0)
                    foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Rows)
                        intResult += base.DeleteRow(drCaseRecord_Leaf, CaseRecord_TreeInfo.GetKey()) ? 1 : 0;

                dtCaseRecord_Tree = _objCaseRecord_Tree.getCaseRecord_TreeDT(strCategoryID);
                if (dtCaseRecord_Tree.Rows.Count == 0)
                {
                    DataTable dtCaseRecord_Category = _objCaseRecord_Category.getCaseRecord_CategoryDT(strCategoryID);
                    if (dtCaseRecord_Category.Rows.Count > 0)
                        intResult += base.DeleteRow(dtCaseRecord_Category.Rows[0], CaseRecord_CategoryInfo.GetKey()) ? 1 : 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intResult;
        }
        #endregion

        #region caseRecord_Category
        public DataTable getSurgicalDT()
        {
            try
            {
                return _objCaseRecord_Category.getSurgicalDT();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region dynamic Table
        public DataTable getPatientDT(String strPatientID)
        {
            try
            {
                return _objDynamicTable.getPatientDT(strPatientID);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet getDataDynamicTableDS(String strCategoryID, String strPatientID, String strOtherID1, String strOtherID2, String strOtherID3)
        {
            try
            {
                DataSet dsReturn = getCaseRecord_TreeDS(strCategoryID);
                DataTable dtCaseRecord_Category = dsReturn.Tables[CaseRecord_CategoryInfo.TABLENAME];
                DataTable dtCaseRecord_Leaf = dsReturn.Tables[CaseRecord_LeafInfo.TABLENAME];
                DataTable dtDynamicTable = null;

                if (dtCaseRecord_Category.Rows.Count > 0)
                {
                    String strTableName = dtCaseRecord_Category.Rows[0][CaseRecord_CategoryInfo.CRC_TABLENAME].ToString();
                    if (_objDynamicTable.isExistsTable(strTableName))
                    {
                        dtDynamicTable = _objDynamicTable.getDataDynamicTableDT(strTableName, strPatientID, strOtherID1, strOtherID2, strOtherID3);
                        foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Rows)
                        {
                            if (!dtDynamicTable.Columns.Contains(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()))
                            {
                                if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                                    dtDynamicTable.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(Double));
                                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                                    dtDynamicTable.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(int));
                                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.YES_NO))
                                    dtDynamicTable.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(String));
                                else
                                    dtDynamicTable.Columns.Add(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString(), typeof(String));
                            }
                        }

                        foreach (DataRow drDynamicTable in dtDynamicTable.Rows)
                        {
                            foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Rows)
                            {
                                if (dtDynamicTable.Columns.Contains(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()))
                                {
                                    if (drDynamicTable[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] == DBNull.Value)
                                    {
                                        if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                                            drDynamicTable[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = 0;
                                        else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                                            drDynamicTable[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = -1;
                                        else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.YES_NO))
                                            drDynamicTable[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = "0";
                                        else
                                            drDynamicTable[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = "";
                                    }
                                }
                            }
                        }
                    }
                    else
                        dtDynamicTable = clsDynamicTable.getStructure(dtCaseRecord_Leaf, strTableName);
                }

                if (dtDynamicTable == null)
                    return null;

                if (dtDynamicTable.Rows.Count == 0)
                {
                    _dynamicTable_AddNewRow(dtDynamicTable, dtCaseRecord_Leaf, strPatientID, strOtherID1, strOtherID2, strOtherID3);
                }
                dsReturn.Tables.Add(dtDynamicTable);
                return dsReturn;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int insertUpdateDynamicTable(String strCategoryID, DataTable dtDynamicTable)
        {
            int intResult = 0;
            try
            {
                if (dtDynamicTable != null && dtDynamicTable.Rows.Count > 0)
                {
                    String strTableName = _createTable(strCategoryID);
                    if (strTableName != "")
                    {
                        dtDynamicTable.TableName = strTableName;
                        foreach (DataRow drDynamicTable in dtDynamicTable.Select(GetUpdatedRowSQL))
                        {
                            if (drDynamicTable[DBBaseInfo.Hidden_Status].ToString().Equals(Convert.ToInt32(GlobalEnumType.Status.REGISTER).ToString()))
                            {
                                intResult += (base.InsertRow(drDynamicTable) ? 1 : 0);
                            }
                            else
                            {
                                intResult += base.UpdateRow(drDynamicTable, clsDynamicTable.getKeys());
                            }
                            drDynamicTable[DBBaseInfo.Hidden_Status] = Convert.ToInt32(GlobalEnumType.Status.HAVE_VALUE).ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intResult;
        }

        public int deleteDynamicTable(String strCategoryID, DataTable dtDynamicTable)
        {
            int intResult = 0;
            try
            {
                if (dtDynamicTable != null && dtDynamicTable.Rows.Count > 0)
                    foreach (DataRow drDynamicTable in dtDynamicTable.Rows)
                        intResult += base.DeleteRow(drDynamicTable, clsDynamicTable.getKeys()) ? 1 : 0;

                if (base.GetData(dtDynamicTable.TableName).Rows.Count <= 0)
                {
                    _objDynamicTable.deleteTable(dtDynamicTable.TableName);
                    _updateUsedStatus(strCategoryID, clsEnumType.BooleanValue.FALES);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intResult;
        }

        #region create Dynamic Table
        private String _createTable(String strCategoryID)
        {
            String strTableName = "";
            DataTable dtCaseRecord_Category = _objCaseRecord_Category.getCaseRecord_CategoryDT(strCategoryID);
            if (dtCaseRecord_Category.Rows.Count > 0)
            {
                strTableName = dtCaseRecord_Category.Rows[0][CaseRecord_CategoryInfo.CRC_TABLENAME].ToString();
                DataTable dtCaseRecord_Tree = _objCaseRecord_Tree.getCaseRecord_TreeDT(strCategoryID);
                DataTable dtCaseRecord_Leaf = _objCaseRecord_Leaf.getCaseRecord_LeafDT(strCategoryID);
                if (_haveNewColumn(dtCaseRecord_Tree, dtCaseRecord_Leaf))
                {
                    if (_objDynamicTable.isExistsTable(strTableName))
                        _objDynamicTable.alterTable(dtCaseRecord_Leaf, strTableName, dtCaseRecord_Tree);
                    else
                        _objDynamicTable.createTable(dtCaseRecord_Leaf, strTableName);
                }
                _updateUsedStatus(strCategoryID, clsEnumType.BooleanValue.TRUE);
            }
            return strTableName;
        }

        private Boolean _haveNewColumn(DataTable dtCaseRecord_Tree, DataTable dtCaseRecord_Leaf)
        {
            //DataRow[] drCaseRecord_LeafFound;
            //foreach (DataRow drCaseRecord_Tree in dtCaseRecord_Tree.Select(CaseRecord_TreeInfo.CRT_ISUSED + " = '" + Convert.ToInt32(clsEnumType.BooleanValue.FALES).ToString() + "'"))
            //{
            //    drCaseRecord_LeafFound = dtCaseRecord_Leaf.Select(CaseRecord_LeafInfo.CRT_ID + " = '" + drCaseRecord_Tree[CaseRecord_TreeInfo.CRT_ID].ToString() + "'");
            //    if (drCaseRecord_LeafFound.Length > 0)
            //        return true;
            //}
            //return false;

            DataRow[] drCaseRecord_TreeFound = dtCaseRecord_Tree.Select(CaseRecord_TreeInfo.CRT_ISUSED + " = '" + Convert.ToInt32(clsEnumType.BooleanValue.FALES).ToString() + "'");
            if (drCaseRecord_TreeFound.Length > 0)
                return true;
            return false;
        }

        private int _updateUsedStatus(String strCategoryID, clsEnumType.BooleanValue status)
        {
            ListDictionary valueUpdate = new ListDictionary();
            valueUpdate.Add(CaseRecord_TreeInfo.CRT_ISUSED, Convert.ToInt32(status).ToString());
            List<ExpressionObject> valueWhere = new List<ExpressionObject>();
            valueWhere.Add(new ExpressionObject(CaseRecord_TreeInfo.CRC_ID, strCategoryID));
            return base.UpdateRow(CaseRecord_TreeInfo.TABLENAME, valueUpdate, valueWhere);
        }

        private void _dynamicTable_AddNewRow(DataTable dtDynamicTable, DataTable dtCaseRecord_Leaf, String strPatientID, String strOtherID1, String strOtherID2, String strOtherID3)
        {
            DataRow drNew = dtDynamicTable.NewRow();
            drNew[clsDynamicTable.PatientID] = strPatientID;
            drNew[clsDynamicTable.OtherID1] = strOtherID1;
            drNew[clsDynamicTable.OtherID2] = strOtherID2;
            drNew[clsDynamicTable.OtherID3] = strOtherID3;
            drNew[clsDynamicTable.Surgical_Date] = DateTime.Now;
            drNew[clsDynamicTable.Surgical_Reason] = "";
            foreach (DataRow drCaseRecord_Leaf in dtCaseRecord_Leaf.Rows)
            {
                if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                    drNew[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = 0;
                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                    drNew[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = -1;
                else if (Convert.ToInt32(drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.YES_NO))
                    drNew[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = "0";
                else
                    drNew[drCaseRecord_Leaf[CaseRecord_LeafInfo.CRL_COLNAME].ToString()] = "";
            }
            drNew[DBBaseInfo.Hidden_Status] = Convert.ToInt32(GlobalEnumType.Status.REGISTER);
            dtDynamicTable.Rows.Add(drNew);
        }
        #endregion
        #endregion

        #region for Statistical data
        public DataTable getStatisticalDataDT(DataSet dsDynamicTable, String  strSurgical_DateFrom, String strSurgical_DateTo)
        {
            try
            {
                String strTableName = "";
                ListDictionary paramWhere = new ListDictionary();
                String strSQL = _getStatisticalDataSQL(dsDynamicTable, ref  strTableName, ref paramWhere, strSurgical_DateFrom, strSurgical_DateTo);
                if (_objDynamicTable.isExistsTable(strTableName))
                {
                    return _objDynamicTable.getStatisticalDataDT(strSQL, strTableName, paramWhere);
                }
                return new DataTable(strTableName);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private String _getStatisticalDataSQL(DataSet dsDynamicTable, ref String strTableName, ref ListDictionary paramWhere, String strSurgical_DateFrom, String strSurgical_DateTo)
        {
            DataTable dtCaseRecord_Category = dsDynamicTable.Tables[CaseRecord_CategoryInfo.TABLENAME];
            DataTable dtCaseRecord_Tree = dsDynamicTable.Tables[CaseRecord_TreeInfo.TABLENAME];
            DataTable dtCaseRecord_Leaf = dsDynamicTable.Tables[CaseRecord_LeafInfo.TABLENAME];
            strTableName = dtCaseRecord_Category.Rows[0][CaseRecord_CategoryInfo.CRC_TABLENAME].ToString();
            DataTable dtDynamicTable = dsDynamicTable.Tables[strTableName];

            String strSQL = "SELECT " + InfoPatients.TABLENAME + ".*, ";
            strSQL += strTableName + ".*";
            strSQL += " FROM " + InfoPatients.TABLENAME;
            strSQL += " INNER JOIN " + strTableName;
            strSQL += " ON " + InfoPatients.TABLENAME + "." + InfoPatients.PATISN + " = " + strTableName + "." + clsDynamicTable.PatientID;

            String strColumnName = "";
            Boolean hasWhere = false;
            DataRow drCaseRecord_LeafFound = null;
            paramWhere = new ListDictionary();

            //column Surgical_Date
            if (strSurgical_DateFrom != "" && strSurgical_DateTo != "")
            {
                strSQL += " WHERE ";
                hasWhere = true;
                strSQL += strTableName + "." + clsDynamicTable.Surgical_Date;
                strSQL += " BETWEEN @From" + clsDynamicTable.Surgical_Date + " AND @To" + clsDynamicTable.Surgical_Date;
                paramWhere.Add("@From" + clsDynamicTable.Surgical_Date, strSurgical_DateFrom);
                paramWhere.Add("@To" + clsDynamicTable.Surgical_Date, strSurgical_DateTo);
            }

            //column Surgical_Reason
            if (dtDynamicTable.Rows[0][clsDynamicTable.Surgical_Reason].ToString() != "")
            {
                if (hasWhere)
                {
                    strSQL += " AND ";
                }
                else
                {
                    strSQL += " WHERE ";
                    hasWhere = true;
                }
                strSQL += strTableName + "." + clsDynamicTable.Surgical_Reason;
                strSQL += " LIKE @" + clsDynamicTable.Surgical_Reason;
                paramWhere.Add("@" + clsDynamicTable.Surgical_Reason, "%" + dtDynamicTable.Rows[0][clsDynamicTable.Surgical_Reason].ToString() + "%");
            }

            //các column động
            foreach (DataColumn dcDynamicTable in dtDynamicTable.Columns)
            {
                strColumnName = dcDynamicTable.ColumnName;
                if (_isExistInDB(strColumnName, dtCaseRecord_Tree, dtCaseRecord_Leaf, ref drCaseRecord_LeafFound) && dtDynamicTable.Rows[0][strColumnName] != null && dtDynamicTable.Rows[0][strColumnName] != DBNull.Value && dtDynamicTable.Rows[0][strColumnName].ToString() != "")
                {
                    if (Convert.ToInt32(drCaseRecord_LeafFound[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.TEXT))
                    {
                        if (hasWhere)
                        {
                            strSQL += " AND ";
                        }
                        else
                        {
                            strSQL += " WHERE ";
                            hasWhere = true;
                        }
                        strSQL += strTableName + "." + strColumnName;
                        strSQL += " LIKE @" + strColumnName;
                        paramWhere.Add("@" + strColumnName, "%" + dtDynamicTable.Rows[0][strColumnName].ToString() + "%");
                    }
                    else if (Convert.ToInt32(drCaseRecord_LeafFound[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.NUMBER))
                    {
                        if (Convert.ToDouble(dtDynamicTable.Rows[0][strColumnName]) != 0)
                        {
                            if (hasWhere)
                            {
                                strSQL += " AND ";
                            }
                            else
                            {
                                strSQL += " WHERE ";
                                hasWhere = true;
                            }
                            strSQL += strTableName + "." + strColumnName;
                            strSQL += " = @" + strColumnName;
                            paramWhere.Add("@" + strColumnName, dtDynamicTable.Rows[0][strColumnName].ToString());
                        }
                    }
                    else if (Convert.ToInt32(drCaseRecord_LeafFound[CaseRecord_LeafInfo.CRL_DATATYPE]) == Convert.ToInt32(clsEnumType.DataType.OPTION))
                    {
                        if (Convert.ToInt32(dtDynamicTable.Rows[0][strColumnName]) != -1)
                        {
                            if (hasWhere)
                            {
                                strSQL += " AND ";
                            }
                            else
                            {
                                strSQL += " WHERE ";
                                hasWhere = true;
                            }
                            strSQL += strTableName + "." + strColumnName;
                            strSQL += " = @" + strColumnName;
                            paramWhere.Add("@" + strColumnName, dtDynamicTable.Rows[0][strColumnName].ToString());
                        }
                    }
                    else
                    {
                        if (hasWhere)
                        {
                            strSQL += " AND ";
                        }
                        else
                        {
                            strSQL += " WHERE ";
                            hasWhere = true;
                        }
                        strSQL += strTableName + "." + strColumnName;
                        strSQL += " = @" + strColumnName;
                        paramWhere.Add("@" + strColumnName, dtDynamicTable.Rows[0][strColumnName].ToString());
                    }
                }
            }
            strSQL += " ORDER BY " + strTableName + "." + clsDynamicTable.Surgical_Date + " DESC";
            return strSQL;
        }

        private Boolean _isExistInDB(String strColumnName, DataTable dtCaseRecord_Tree, DataTable dtCaseRecord_Leaf, ref DataRow drCaseRecord_LeafFound)
        {
            DataRow[] drCaseRecord_LeafFoundArr = dtCaseRecord_Leaf.Select(CaseRecord_LeafInfo.CRL_COLNAME + " = '" + strColumnName + "'");
            if (drCaseRecord_LeafFoundArr.Length > 0)
                if (dtCaseRecord_Tree.Select(CaseRecord_TreeInfo.CRT_ID + " = '" + drCaseRecord_LeafFoundArr[0][CaseRecord_LeafInfo.CRT_ID].ToString() + "' AND " + CaseRecord_TreeInfo.CRT_ISUSED + " = '" + Convert.ToInt32(GlobalEnumType.BooleanStatus.TRUE).ToString() + "'").Length > 0)
                {
                    drCaseRecord_LeafFound = drCaseRecord_LeafFoundArr[0];
                    return true;
                }
            drCaseRecord_LeafFound = null;
            return false;
        }
        #endregion
    }
}